/*
 *    This file is part of BlobDB.
 *
 *    BlobDB is free software: you can redistribute it and/or modify
 *    it under the terms of the GNU General Public License as published by
 *    the Free Software Foundation, either version 3 of the License, or
 *    (at your option) any later version.
 *
 *    BlobDB is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *    GNU General Public License for more details.
 *
 *    You should have received a copy of the GNU General Public License
 *    along with BlobDB.  If not, see <http://www.gnu.org/licenses/>.
 *    
 *    Copyright(c) 2011 Michael Kankkonen
 *    http://www.vnetcon.org
 */


package org.vnetcon.blobdb.database;

import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.List;
import java.util.Date;
import java.util.Properties;


import javax.script.Invocable;
import javax.script.ScriptEngine;
import javax.script.ScriptEngineFactory;
import javax.script.ScriptEngineManager;

import org.vnetcon.blobdb.database.backend.KeyBuilderInterface;
import org.vnetcon.blobdb.database.backend.KeyInterface;
import org.vnetcon.blobdb.database.backend.PersistenceManagerInterface;
import org.vnetcon.blobdb.database.backend.QueryInterface;
import org.vnetcon.blobdb.database.backend.PMFInterface;
import org.vnetcon.blobdb.database.backend.QueueEntityInterface;
import org.vnetcon.blobdb.database.backend.TableInterface;
import org.vnetcon.blobdb.database.backend.TableDataInterface;
import org.vnetcon.blobdb.database.backend.TableIDInterface;
import org.vnetcon.blobdb.database.backend.file.FileKeyBuilder;
import org.vnetcon.blobdb.database.backend.file.FilePMF;
import org.vnetcon.blobdb.database.backend.file.FileTable;
import org.vnetcon.blobdb.database.backend.file.FileTableData;
import org.vnetcon.blobdb.database.backend.file.FileTableID;
import org.vnetcon.blobdb.database.backend.gae.GAEKeyBuilder;
import org.vnetcon.blobdb.database.backend.gae.GAEPMF;
import org.vnetcon.blobdb.database.backend.gae.GAETable;
import org.vnetcon.blobdb.database.backend.gae.GAETableData;
import org.vnetcon.blobdb.database.backend.gae.GAETableID;
import org.vnetcon.blobdb.database.backend.memory.MemoryKeyBuilder;
import org.vnetcon.blobdb.database.backend.memory.MemoryPMF;
import org.vnetcon.blobdb.database.backend.memory.MemoryTable;
import org.vnetcon.blobdb.database.backend.memory.MemoryTableData;
import org.vnetcon.blobdb.database.backend.memory.MemoryTableID;

import org.vnetcon.blobdb.database.util.ColumnInfo;
import org.vnetcon.blobdb.database.util.InfoBase;
import org.vnetcon.blobdb.database.util.SQLParser;
import org.vnetcon.blobdb.database.util.SortVector;
import org.vnetcon.blobdb.database.util.TableRow;

import org.mozilla.javascript.*;

/**
 * 
 * This is BlobDB main class. Developers will create an instance of this class when they us BlobDB.
 * 
 *
 */
public class Statement {

//	PersistenceManagerInterface pm = null;

	
	/**
	 * This is the default back end type (Google App Engine datastore)
	 */
	public static int BACKEND_GAE = 1;
	/**
	 * This is for storing data in files instead of Google App Engine entities.
	 * This mode can be used outside of GAE - e.g. under Tomcat, Glassfish etc.
	 * THIS IS NOT IMPLEMENTED YET!
	 */
	public static int BACKEND_FILE = 2;
	/**
	 * This mode will run BlobeDB in memory mode. This mode can be used in 
	 * any kind of java applications.
	 */
	public static int BACKEND_MEMORY = 3;
	/**
	 * This mode will run BlobeDB in gateway mode. The back end for this
	 * type can be native GAE datastore, RDBMS database, file system etc.
	 * The actual implementation of this depends on GatewayInterface implementations.
	 * The "sql" parse and return values (String or ResultSet) will be done there.
	 */
	public static int BACKEND_GATEWAY = 4;
	
	private int use_backend = BACKEND_GAE;

	private TableInterface createNewTable = null;
	private TableDataInterface createNewTableData = null;
	private TableIDInterface createNewTableID = null;
	private KeyBuilderInterface createNewKeyBuilder = null;
	
	private static int DATA_MAX = 800000;
	private int ROWSIZE_MAX = 1000;
	private int ROWCOUNT_MAX = 1000;
	private int UPDATE_DELAY = 500;

	// this is sql based case to avoid multiple calls for same query
//	private Hashtable<String, ResultSet> rsCache = null;
	private SQLParser parser = null;
	private String schema = null;
	private Hashtable<String, TableDataInterface> transactionTables = null;
	private List<String[]> orderBy = null;
	private Hashtable<Integer, List<byte[]>> selectTableDataCache = null;
	private Hashtable<String, List<TableInterface>> resultsCache = null;
	private List<String> gSelectCols = null;
	
	ScriptEngine jsEngineMgr = null;
	Invocable ijsEngineMgr = null;

	
	// Rhino version
	Context jsEngine = null;
	Scriptable ijsEngine = null; 
	Function whereFunction = null;
	
	
	public void finalize(){
		parser = null;
		schema = null;
		transactionTables = null;
		orderBy = null;
		selectTableDataCache = null;
		resultsCache = null;
		jsEngine = null;
		ijsEngine = null; 
		whereFunction = null;
		jsEngineMgr = null;
		ijsEngineMgr = null;
		createNewTable = null;
		createNewTableData = null;
		createNewTableID = null;
		createNewKeyBuilder = null;
//		pm = null;
		Runtime.getRuntime().gc();
	}
	
	private int useRhino = 1;

	long start = 0;
	long end = 0;
	int rangeStart = -1;
	int rangeEnd = -1;
	int addedResults = 0;
	int resultStart = 0;
	int resultCount = 2000;
	
	boolean useTableLock = true;
	boolean ignoreCaseInSort = false;
	WsAuthenticationInterface auth = null;

	AuditTrailInterface audit = null;
	String auditTrailUser = "unknown";

	GatewayInterface gatewayInterface = null;
	
//	ArrayList<Result> resultSet = new ArrayList<Result>();
	SortVector resultSet = new SortVector();

	/**
	 * Default constructor for users. This will connect to GAE datastore. 
	 */
	public Statement() throws Exception {
		this.schema = SQLParser.DEFAULT_SCHEMA;
		initMode();
		try{
			this.loadAuditTrail();
		}catch(Exception ex){
			System.out.println("Failed to load audit trail class.");
//			ex.printStackTrace();
		}
	}

	/**
	 * Use this if you want to use BlobDB in memory only mode.
	 */
/*
	public Statement(int backend_type) throws Exception {
		if(backend_type != BACKEND_GAE && backend_type != BACKEND_MEMORY && backend_type != BACKEND_FILE  && backend_type != BACKEND_GATEWAY){
			throw new Exception("Backend must be 1 = BACKEND_GAE or 2 = BACKEND_FILE or 3 = BACKEND_MEMORY or 4 = BACKEND_GATEWAY");
		}

		initMode();
		this.schema = SQLParser.DEFAULT_SCHEMA;
		this.use_backend = backend_type;
		initBackend();
		try{
			this.loadAuditTrail();
		}catch(Exception ex){
			System.out.println("Failed to load audit trail class.");
//			ex.printStackTrace();
		}
	}
	*/

	/**
	 * Set the user which will be used when calling audit trail methods from implemented interface.
	 * In case the implementation of this interface is missing or wrong this parameter doesn't have
	 * any affect.
	 * 
	 * @param user
	 */
	public void setAuditTraiUser(String user){
		this.auditTrailUser = user;
	}

	/**
	 * Returns the statements audit trail user. Default is "unknown".
	 * 
	 * @return
	 */
	public String getAuditTrailUser(){
		return this.auditTrailUser;
	}

	
	private void initMode() throws Exception {
		String gatewayClass = null;
		InputStream fIn = this.getClass().getResourceAsStream("/blobdb/blobdb.properties");
		BufferedReader bfIn = new BufferedReader(new InputStreamReader(fIn));
		Properties p = new Properties();
		p.load(bfIn);
		this.use_backend = Integer.parseInt(p.getProperty("ws.mode", "1"));
		DATA_MAX = Integer.parseInt(p.getProperty("block.size", "800000"));
		UPDATE_DELAY = Integer.parseInt(p.getProperty("update.delay", "500"));
		this.resultCount = Integer.parseInt(p.getProperty("sortbuffer.size", "2000"));
		this.useRhino = Integer.parseInt(p.getProperty("javascript.engine", "1"));
		gatewayClass = p.getProperty("gateway.class", null);
		bfIn.close();
		if(this.use_backend == BACKEND_GATEWAY){
			this.gatewayInterface = (GatewayInterface)Class.forName(gatewayClass).newInstance();
		}
	}
	

	private WsAuthenticationInterface loadAuth() throws Exception {
		
		if(this.auth != null){
			return this.auth;
		}
		
		WsAuthenticationInterface authRet = null;
		InputStream fIn = this.getClass().getResourceAsStream("/blobdb/blobdb.properties");
		BufferedReader bfIn = new BufferedReader(new InputStreamReader(fIn));
		Properties p = new Properties();
		p.load(bfIn);
		String authClass = p.getProperty("ws.auth.class");
		bfIn.close();
		authRet = (WsAuthenticationInterface)Class.forName(authClass).newInstance();
		return authRet;
	}

	private AuditTrailInterface loadAuditTrail() throws Exception {
		if(this.audit != null){
			return this.audit;
		}
		AuditTrailInterface auditRet = null;
		InputStream fIn = this.getClass().getResourceAsStream("/blobdb/blobdb.properties");
		BufferedReader bfIn = new BufferedReader(new InputStreamReader(fIn));
		Properties p = new Properties();
		p.load(bfIn);
		String auditClass = p.getProperty("audit.trail.class");
		bfIn.close();
		auditRet = (AuditTrailInterface)Class.forName(auditClass).newInstance();
		return auditRet;
	}
	
	
	private void initBackend() throws Exception {

		if(this.use_backend == BACKEND_GATEWAY){
			this.initMode();
		}
		
		
		if(createNewTable != null){
			return;
		}

		if(this.use_backend == BACKEND_GAE){
			createNewTable = new GAETable(null);
			createNewTableData = new GAETableData(null);
			createNewTableID = new GAETableID(null);
			createNewKeyBuilder = new GAEKeyBuilder();
		}
		
		if(this.use_backend == BACKEND_FILE){
			createNewTable = new FileTable(null);
			createNewTableData = new FileTableData(null);
			createNewTableID = new FileTableID(null);
			createNewKeyBuilder = new FileKeyBuilder();
		}

		if(this.use_backend == BACKEND_MEMORY){
			createNewTable = new MemoryTable(null);
			createNewTableData = new MemoryTableData(null);
			createNewTableID = new MemoryTableID(null);
			createNewKeyBuilder = new MemoryKeyBuilder();
		}
		
	}

	/**
	 * If you want to set used back end later, use this method to do this.
	 * 
	 * @param backend
	 * @throws Exception
	 */
	public void setUsedBackend(int backend) throws Exception {
		if(backend != BACKEND_GAE && backend != BACKEND_MEMORY && backend != BACKEND_GATEWAY && backend != BACKEND_FILE){
			throw new Exception("Backend must be 1 = BACKEND_GAE or 2 = BACKEND_FILE or 3 = BACKEND_MEMORY or 4 = BACKEND_GATEWAY");
		}
		this.use_backend = backend;
	}
	
	/**
	 * Return the used back end type.
	 * 
	 * @return
	 */
	public int getUsedBackend(){
		return this.use_backend;
	}

	/**
	 * BlobDB uses java sort based on string values. With this you can change the behaviour of sorting.
	 * 
	 * @param ignore
	 */
	public void setIgnoreCaseInOrder(boolean ignore){
		this.ignoreCaseInSort = ignore;
	}

	/**
	 * By default BlobDB executes all updates under transaction. 
	 * If you want to disable this set this value to false.
	 * 
	 * @param useTableLock
	 */
	public void setTableLock(boolean useTableLock){
		this.useTableLock = useTableLock;
	}
	
	public boolean getTableLock(){
		return this.useTableLock;
	}
	
	private PersistenceManagerInterface getPersistenceManager(boolean useTransaction) throws SQLException {
		PMFInterface pmf = null;
		PersistenceManagerInterface pm = null;
		transactionTables = new Hashtable<String, TableDataInterface>();
//		rsCache = new Hashtable<String, ResultSet>();
		if(selectTableDataCache == null){
			selectTableDataCache = new Hashtable<Integer, List<byte[]>>();
		}

		if(resultsCache == null){
			resultsCache = new Hashtable<String, List<TableInterface>>();
		}
		
		parser = new SQLParser();

		if(this.use_backend == BACKEND_GAE){
			pmf = new GAEPMF();
		}

		if(this.use_backend == BACKEND_FILE){
			pmf = new FilePMF();
		}
		
		if(this.use_backend == BACKEND_MEMORY){
			pmf = new MemoryPMF();
		}
		
		try {
			this.initBackend();
		} catch (Exception e) {
			throw new SQLException("Backend initialazion failed: " + e.getMessage());
		}
		
		try{
			//pm = PMF.get().getPersistenceManager();
			pm = pmf.getPersistenceManager();
			if(useTransaction && useTableLock){
				try{
					pm.waitForMyTurn(new java.util.Date().getTime(), this.UPDATE_DELAY, -1, -1);
				}catch(Exception ex){
					pm.doneWithUpdate();
					pm.close();
					throw new Exception("Couldn't get turn for upadte: " + ex.getMessage());
				}
				pm.currentTransaction().begin();
			}
		}catch(Exception ex){
			pm.close();
			ex.printStackTrace();
			throw new SQLException(ex.getMessage());
		}
		return pm;
	}	
	
	private void close(PersistenceManagerInterface pm, boolean useTransaction, boolean commit) throws SQLException, SQLTransactionException {
		if(useTransaction && useTableLock){
			try{
				if(commit){
					pm.currentTransaction().commit();
				}else{
					pm.currentTransaction().rollback();
				}
			}catch(Exception ex){
				this.transactionTables.clear();
				pm.doneWithUpdate();
				pm.close();
				pm = null;
				Runtime.getRuntime().gc();
				throw new SQLTransactionException("Commit for data failed: " + ex);
			}
		}
		this.transactionTables.clear();
		pm.doneWithUpdate();
		pm.close();
		pm = null;
		Runtime.getRuntime().gc();
	}
	

	/**
	 * By default BlobDB limits the ResultSet size to 2000. If you want to increase this
	 * call this method with values you want to use. 
	 * 
	 * You can also give range here (e.g. 51-100, 101-150 etc.) It is good to know that
	 * if you give the range here it will not decrease the size of sort buffer.
	 * The buffer will hold the biggest value you give here always from 0 to range max
	 * and this is truncated when returning results.
	 * 
	 * @param bSQL
	 * @param resultStart
	 * @param resultCount
	 * @return
	 * @throws SQLException
	 * @throws SQLTransactionException
	 * @throws Exception
	 */
	public ResultSet executeQuery(String bSQL, int resultStart, int resultCount) throws SQLException, SQLTransactionException, Exception {
		if(this.use_backend == BACKEND_GATEWAY){
			this.checkQueryAudit(bSQL);
			return this.gatewayInterface.executeQuery(bSQL, resultStart, resultCount);
		}
		this.resultStart = resultStart;
		this.resultCount = resultCount + resultStart;
		return this.executeQuery(bSQL);
	}	

	private ArrayList<Result> getTableCols(PersistenceManagerInterface pm, String tableName, String schema) throws SQLException {
		ArrayList<Result> tables = new ArrayList<Result>();
		QueryInterface query = pm.newQuery(TableInterface.class);
		List<TableInterface> results = (List<TableInterface>) query.execute(tableName, schema);
		ArrayList<String> order = new ArrayList<String>();
		ArrayList<String> selectCols = new ArrayList<String>();
		int i = 0;
		
//		order.add("NAME");
		selectCols.add("NAME");
		selectCols.add("TYPE");
		selectCols.add("SIZE");
		selectCols.add("DECIMALS");
		while(i < results.size()){
			int ii = 0;
			TableInterface table = results.get(i);
			ColumnInfo ci = new ColumnInfo();
			ci.parseInfo(table.getBytes());
			List<String> cols = ci.getColumnNames();
			while(ii < cols.size()){
				String colName = cols.get(ii);
				Result r = new Result();
				r.setSelectCols(selectCols);
				Hashtable<String, String> hcols = new Hashtable<String, String>();
				hcols.put("NAME", colName);
				hcols.put("TYPE", "" + ci.getColumnType(colName));
				hcols.put("SIZE", "" + ci.getColumnLength(colName));
				hcols.put("DECIMALS", "" + ci.getColumnDecimals(colName));
				r.setColumnValues(hcols);
				tables.add(r);
				ii++;
			}
			
			i++;
		}
		
		return tables;
	}

	private boolean checkQueryAudit(String sql) throws SQLException{
		if(this.audit != null){
			if(!audit.executingQuery(this.auditTrailUser, sql)){
				throw new SQLException("Audit trail says now for this request for user " + this.auditTrailUser);
			}
		}
		return true;
	}

	
	private boolean checkUpdateAudit(String sql) throws SQLException{
		if(this.audit != null){
			if(!audit.executingQuery(this.auditTrailUser, sql)){
				throw new SQLException("Audit trail says now for this request for user " + this.auditTrailUser);
			}
		}
		return true;
	}

	/**
	 * This method is for executing select statements and desc table statement.
	 * 
	 * @param bSQL
	 * @return
	 * @throws SQLException
	 * @throws SQLTransactionException
	 * @throws Exception
	 */
	public ResultSet executeQuery(String bSQL) throws SQLException, SQLTransactionException, Exception {
		this.checkQueryAudit(bSQL);
		
		if(this.use_backend == BACKEND_GATEWAY){
			return this.gatewayInterface.executeQuery(bSQL);
		}

		start = new Date().getTime();
		this.jsEngine = null;
		ColumnInfo selectColInfo = new ColumnInfo();
		int i = 0;
		int iTableLoop = 0;
		PersistenceManagerInterface pm = this.getPersistenceManager(false);
		int iType = parser.parse(bSQL);
		this.schema = parser.getSchema();
		this.orderBy = parser.getOrderBy();
		this.rangeStart = parser.getRangeStart();
		this.rangeEnd = parser.getRangeEnd();
		String tableName = parser.getTableName();
//		List<String> selectColumns = parser.getSelectColumns();
		gSelectCols = parser.getSelectColumns();
		List<TableDataInterface> tableData = null;
		List<String> subQueryStrings = parser.getSubQueries();
		TableInterface selectTable = null;
		ColumnInfo colInfo = new ColumnInfo();
//		boolean bColInfoParsed = false;
		ResultSet retResultSet = null;
		String rowid = parser.getRowid();
		boolean bColInfoParsed = false;
		
		//resultData = null;
//		resultSet = new ArrayList<Result>();
		resultSet = new SortVector();
		this.addedResults = 0;

		
		if(rowid != null){
			//TODO: implement select based on rowid including sub queries
		}
		

		if(iType == SQLParser.DESC_TABLE_TYPE){
			retResultSet = new ResultSet();
			try {
				ArrayList<Result> tables = this.getTableCols(pm, parser.getTableName(), parser.getSchema());
				end = new Date().getTime();
//				Collections.sort(tables);
				retResultSet.setResults(tables);
				retResultSet.setStatusMessage("ok:");
				retResultSet.setExecutionTime(end - start);
			} catch (Exception ex) {
				throw new SQLException("Desc table failed: " + ex.getMessage());
			}
			return retResultSet;
		}

		List<TableInterface> results = null;
		if(!this.resultsCache.containsKey(this.schema + "." + tableName)){
			QueryInterface query = pm.newQuery(TableInterface.class);
			if (this.schema.equals("*")) {
				query.setFilter("tableName == tableNameParam");
				query.declareParameters("String tableNameParam");
				results = (List<TableInterface>) query.execute(tableName);
				if (results.size() < 1) {
					throw new SQLException("Table does not exists: " + schema
							+ "." + tableName);
				}
			} else {
				query.setFilter("tableName == tableNameParam && schema == schemaParam");
				query.declareParameters("String tableNameParam, String schemaParam");
				results = (List<TableInterface>) query.execute(tableName, schema);
				if (results.size() < 1) {
					throw new SQLException("Table does not exists: " + schema
							+ "." + tableName);
				}
			}
			this.resultsCache.put(this.schema + "." + tableName, results);
		}
		results = resultsCache.get(this.schema + "." + tableName);
		
		iTableLoop = 0;
//		while (iTableLoop < resultsCache.size()) {
		while (iTableLoop < results.size()) {

			// selectTable = results.get(0);
//			selectTable = resultsCache.get(iTableLoop);
//			selectTable = resultsCache.get(this.schema + "." + tableName).get(iTableLoop);
			selectTable = results.get(iTableLoop);
			this.ROWSIZE_MAX = selectTable.getRowsize();
			
			if(!bColInfoParsed){
				colInfo.parseInfo(selectTable.getBytes());
				i = 0;
//				while(i < selectColumns.size()){
				while(i < gSelectCols.size()){
//					String selColName = selectColumns.get(i);
					String selColName = gSelectCols.get(i);
					selectColInfo.addColumn(selColName, colInfo.getColumnType(selColName), colInfo.getColumnLength(selColName), colInfo.getColumnDecimals(selColName));
					i++;
				}
				bColInfoParsed = true;
			}
			
			// testing column names
			i = 0;
//			while (i < selectColumns.size()) {
			while (i < gSelectCols.size()) {
//				String col = selectColumns.get(i);
				String col = gSelectCols.get(i);
				selectColInfo.getColumnIndex(col);
				i++;
			}
			
			List<byte[]> cacheBytes = new ArrayList<byte[]>();
			if(!this.selectTableDataCache.containsKey(selectTable.getTableID())){
//				System.out.println("ADDING cache bytes...");
				tableData = getTableData(selectTable.getTableID(), pm);
				int ii = 0;
				while(ii < tableData.size()){
					cacheBytes.add(tableData.get(ii).getBytes(false));
					ii++;
				}
				this.selectTableDataCache.put(selectTable.getTableID(), cacheBytes);
			}else{
//				System.out.println("GETTING cache bytes...");
				cacheBytes = this.selectTableDataCache.get(selectTable.getTableID());
			}

			i = 0;
//			int iBuf = tableData.size();
			int iBuf = cacheBytes.size();
			while (i < iBuf) {
				int ii = 0;
//				byte[] dataSet = tableData.get(i).getBytes(false);
				byte[] dataSet = cacheBytes.get(i);
				byte[][] rows = getRows(dataSet);

				while (ii < rows.length) {
//					handleWherePart(rows[ii], colInfo, subQueryStrings, selectColumns);
					handleWherePart(rows[ii], colInfo, subQueryStrings, gSelectCols);
					ii++;
				}
				i++;
			}

			iTableLoop++;
		}
	    this.close(pm, false, false);
		end = new Date().getTime();
		
		retResultSet = new ResultSet();
		Collections.sort(this.resultSet);
		retResultSet.setExecutionTime(end - start);
		retResultSet.setSelectCols(gSelectCols);
		
//		if(this.resultSet.size() > this.resultStart + this.resultCount){
		if(this.resultSet.size() > this.resultCount){
			this.resultSet.removeElements(this.resultCount, this.resultSet.size());
		}

		if(this.rangeStart > -1 && this.rangeEnd > -1){
			if(this.rangeStart < this.resultSet.size()){
				this.resultSet.removeElements(0, this.rangeStart);
			}else{
				this.resultSet.clear();
			}
			if(this.rangeEnd - this.rangeStart < this.resultSet.size()){
				this.resultSet.removeElements(this.rangeEnd - this.rangeStart, this.resultSet.size());
			}
		}
				
		retResultSet.setResults(this.resultSet);
		return retResultSet;
	}

	private String replaceParams(byte[] dataRow, Hashtable<String, String> rowValues, String subQuery) throws SQLException {
		String strRet = subQuery;
		Enumeration<String> cols = rowValues.keys();
		int i = 0;

		while(cols.hasMoreElements()){
			StringBuffer sb = new StringBuffer();
			StringBuffer sb2 = new StringBuffer();
			String colName = cols.nextElement();
			String colValue = rowValues.get(colName);
			
//			System.out.println("COLNAME IN REPLACE: " + colName + " value: " + colValue);
			sb.append("_");
			sb.append(colName);
			sb.append("_");
//			sb2.append("'");
			sb2.append(colValue);
//			sb2.append("'");
			strRet = strRet.replaceAll(sb.toString(), sb2.toString());
//			strRet = strRet.replaceAll("_" + colName + "_", "'" + colValue + "'");
			i++;
		}
//		System.out.println(" strRet: " + strRet);
		return strRet;
	}
	
	protected void setSelectTableCache(Hashtable<Integer, List<byte[]>> cacheData, Hashtable<String, List<TableInterface>> resultsCache){
		this.selectTableDataCache = cacheData;
		this.resultsCache = resultsCache;
	}
	
	private ArrayList<ResultSet> getSubQueries(byte[] dataRow, ColumnInfo ci, List<String> subQueries, Hashtable<String, String> rowValues) throws SQLException, SQLTransactionException, Exception {
//		Statement stmt = new Statement(this.use_backend);
		Statement stmt = new Statement();
		stmt.setSelectTableCache(this.selectTableDataCache, this.resultsCache);
		ArrayList<ResultSet> ars = new ArrayList<ResultSet>();
		
		int i = 0;
		while(i < subQueries.size()){
			String sSql = subQueries.get(i);
			this.checkQueryAudit(sSql);
//			sSql = replaceParams(dataRow, ci, sSql);
			sSql = replaceParams(dataRow, rowValues, sSql);
			ars.add(stmt.executeQuery(sSql));
			i++;
		}
		return ars;
	}

	private String getRowIdAsString(byte[] dataRow, ColumnInfo ci){
		int iTableId = ci.readInt(dataRow, 0);
		int iTableDataId = ci.readInt(dataRow, 4);
		int iRowId = ci.readInt(dataRow, 8);
		int iDate = ci.readInt(dataRow, 12);
		int iTime = ci.readInt(dataRow, 16);
		int iMills = ci.readInt(dataRow, 20);
		StringBuilder sb = new StringBuilder();
		sb.append(iTableId);
		sb.append(".");
		sb.append(iTableDataId);
		sb.append(".");
		sb.append(iRowId);
		sb.append(".");
		sb.append(iDate);
		sb.append(".");
		if(iTime < 100000){
			sb.append("0");
		}
		sb.append(iTime);
		sb.append(".");
		sb.append(iMills);
		return sb.toString();
	}
	
	private void handleSortPart(byte[] dataRow, ColumnInfo ci, List<String> subQueryStrings, Hashtable<String, String> rowValues, List<String> selectCols) throws SQLException, SQLTransactionException, Exception {
		Result r = new Result();
		r.setRowid(this.getRowIdAsString(dataRow, ci));
		if(subQueryStrings.size() > 0){
//			long start = new java.util.Date().getTime();
			r.setSubqueries(this.getSubQueries(dataRow, ci, subQueryStrings, rowValues));
//			long end = new java.util.Date().getTime();
//			System.out.println("set subqueries time: " + (end - start));
		}
		r.setRawData(dataRow, ci);
		r.setOrderBy(this.orderBy);
//		r.setSelectCols(selectCols);
		r.setSelectCols(gSelectCols);
		r.setColumnValues(rowValues);
		r.setIgnoreCase(this.ignoreCaseInSort);
		this.resultSet.add(r);

//		if(this.resultSet.size() > this.resultCount){
		if(this.resultSet.size() > this.ROWCOUNT_MAX + 100){
			Collections.sort(this.resultSet);
//			this.resultSet = new ArrayList<Result>(this.resultSet.subList(0, this.resultCount));
//			this.resultSet.remove(this.resultSet.size() - 1);
			this.resultSet.removeElements(0, this.ROWCOUNT_MAX);
		}

		this.addedResults++;
	}
	
	private boolean evaluateWhere(ColumnInfo ci, String strWhere, Hashtable<String, String> rowValues) throws SQLException{
		
		if(strWhere == null){
			return true;
		}
		
		if(jsEngine == null && this.useRhino == 1){
			initJsEngine(ci, strWhere);
		}

		if(jsEngineMgr == null && this.useRhino == 2){
			initJsEngine(ci, strWhere);
		}
		
		try{
			List<String> acolumns = ci.getColumnNames();
			String columns[] = new String[acolumns.size()];
			int i = 0;
			
			while(i < acolumns.size()){
				String value = rowValues.get(acolumns.get(i));
				columns[i] = value;
				i++;
			}

//			return (Boolean)ijsEngine.invokeFunction("where", (Object[])columns);
//			return (Boolean)jsEngine.evaluateString(ijsEngine, this.whereFunction, "where", 1, null);
			
			if(this.useRhino == 1){
				return (Boolean)this.whereFunction.call(jsEngine, ijsEngine, ijsEngine, columns);
			}
			
			if(this.useRhino == 2){
				return (Boolean)ijsEngineMgr.invokeFunction("where", (Object[])columns);
			}
			
			throw new SQLException("Where part handling failed because of JavaScript engine configuration.");
			
		}catch(Exception ex){
			throw new SQLException("Failed to execute where part: " + ex.getMessage());
		}
	}

	private String getJsFunction(ColumnInfo ci, String strWhere) throws SQLException {
		String strRet = "function where(";
		try{
			List<String> cols = ci.getColumnNames();
			int i = 0;
			String comma = "";
			while(i < cols.size()){
				strRet += comma + cols.get(i);
				comma = ", ";
				i++;
			}
			
			
		}catch(Exception ex){
			throw new SQLException(ex.getMessage());
		}
		strRet += "){\n\n";
		strRet += "if(";
		strRet += strWhere;
		strRet += "){\nreturn true;\n}else{\nreturn false;\n}\n}";

		strRet = fixWsMarks(strRet);
//		System.out.println(strRet);
		return strRet;
	}

	public String fixWsMarks(String str){
		String strRet = str;
		strRet = strRet.replaceAll("&quot;", "\"");
		strRet = strRet.replaceAll("&amp;", "&");
		strRet = strRet.replaceAll("&apos;", "'");
		strRet = strRet.replaceAll("&lt;", "<");
		strRet = strRet.replaceAll("&gt;", ">");

		strRet = strRet.replaceAll("&QUOT ;", "\"");
		strRet = strRet.replaceAll("&AMP ;", "&");
		strRet = strRet.replaceAll("&APOS ;", "'");
		strRet = strRet.replaceAll("&LT ;", "<");
		strRet = strRet.replaceAll("&GT ;", ">");
		strRet = strRet.replaceAll("& &", "&&");
		return strRet;
	}
	
	private void initJsEngine(ColumnInfo ci, String strWhere) throws SQLException {
		try {
			String strFunction = this.getJsFunction(ci, strWhere);
			
			if (this.useRhino == 2) {
				ScriptEngineManager mgr = new ScriptEngineManager();

				List<ScriptEngineFactory> factories = mgr.getEngineFactories();

				jsEngineMgr = mgr.getEngineByName("JavaScript");
				ijsEngineMgr = (Invocable) jsEngine;
				jsEngineMgr.eval(strFunction);

			}
			
			if(this.useRhino == 1){
				jsEngine = Context.enter();
				jsEngine.setLanguageVersion(Context.VERSION_1_2);
				ijsEngine = jsEngine.initStandardObjects();
				this.whereFunction = jsEngine.compileFunction(ijsEngine, strFunction, "where", 1, null);
			}
		} catch (Exception ex) {
//			throw new SQLException("Failed to build where function: " + ex.getMessage());
			ByteArrayOutputStream bOut = new ByteArrayOutputStream();
			PrintStream stream = new PrintStream(bOut);
			ex.printStackTrace(stream);
			throw new SQLException("Failed to build where function: " + "\n\n\n" + new String(bOut.toByteArray()));
		}
	}
	
	
	/**
	 * This is the place where rowid byte array is converted to Hashtable and later to WSResult
	 */
	private void handleWherePart(byte[] dataRow, ColumnInfo ci, List<String> subQueryStrings, List<String> selectCols) throws SQLException, SQLTransactionException, Exception {
		TableRow tr = new TableRow(dataRow, this.ROWSIZE_MAX);
		List<String> cols = ci.getColumnNames();
		Hashtable<String, String> rowValues = new Hashtable<String, String>();
		int i = 0;
		
		// moving values from bytearray to hashtable
		while(i < cols.size()){
			String colName = cols.get(i);
			byte buf[] = tr.getColBytes(ci.getColumnStartPosition(colName), ci.getColumnLength(colName));
			String colValue = new String(buf);
			rowValues.put(colName, colValue);
//			ci.setColValue(colName, colValue);
			i++;
		}
		// adding rowid value to hahstable
		rowValues.put("ROWID", tr.getRowIDString());
				
		if(evaluateWhere(ci, parser.getWherePart(), rowValues)){
			handleSortPart(dataRow, ci, subQueryStrings, rowValues, selectCols);
		}
	}
	
	private byte[][] getRows(byte[] data) throws SQLException{
//		ColumnInfo ci = new ColumnInfo();
//		int iRowCount = ci.readInt(data, 0);
		int iRowCount = InfoBase.readInt(data, 0);
		byte[][] rows = new byte[iRowCount][ROWSIZE_MAX + TableRow.ROWID_SIZE];
		int iCursor = 4;
		int i = 0;
		try{
			while(i < iRowCount){
//				rows[i] = ci.readBytes(data, iCursor, ROWSIZE_MAX + TableRow.ROWID_SIZE);
				rows[i] = InfoBase.readBytes(data, iCursor, ROWSIZE_MAX + TableRow.ROWID_SIZE);
				iCursor = iCursor + ROWSIZE_MAX + TableRow.ROWID_SIZE;
				i++;
			}
		}catch(Exception ex){
			throw new SQLException(ex.getMessage());
		}
		return rows;
	}
	
	private synchronized List<TableDataInterface> getTableData(int tableID, PersistenceManagerInterface pm) throws SQLException, SQLTransactionException {
		List<TableDataInterface> results = null;
		try{
			QueryInterface query2 = pm.newQuery(TableDataInterface.class);
			query2.setFilter("tableID == tableIDParam");
			query2.declareParameters("Integer tableIDParam");
			results = (List<TableDataInterface>)query2.execute(tableID);
		}catch(Exception ex){
			throw new SQLException(ex.getMessage());
		}

	    return results;
	}

	/**
	 * This method is used if the query request have come from WebService.
	 * Before executing any statements BlobDB will ask permission from implemented
	 * WsAuthentication class. If the class is not available BlobDB will not execute
	 * andy statements.
	 * 
	 * @param bSQL
	 * @param username
	 * @param password
	 * @return
	 * @throws SQLException
	 * @throws SQLTransactionException
	 */
	public ResultSet executeQueryFromWebService(String bSQL, String username, String password) throws SQLException, SQLTransactionException{
		try {
			bSQL = new String(bSQL.getBytes(), "UTF-8");
		} catch (UnsupportedEncodingException e) {
		}
		ResultSet rs = new ResultSet();
		String strBuf = "";

		try{
			auth = loadAuth();
		}catch(Exception ex){
			throw new SQLException("Failed to load authentication class: " + ex.getMessage());
		}
		
		if(auth == null){
			strBuf = "Authentication module is missing. Please implement IWsAuthentcation and configure it for WSS";
			throw new SQLException(strBuf);
		}
		
		if(!auth.allow(username, password, bSQL)){
			strBuf = "Access denied.";
			throw new SQLException(strBuf);
		}
		
		try{
			rs = executeQuery(bSQL);
			rs.setStatusMessage("ok:");
		}catch(Exception ex){
			rs.setStatusMessage("error: " + ex.getLocalizedMessage());
		}
		return rs;
	}

	public ResultSet executeQueryFromWebService(String bSQL, String username, String password, int rangeStart, int rangeEnd) throws SQLException, SQLTransactionException{
		try {
			bSQL = new String(bSQL.getBytes(), "UTF-8");
		} catch (UnsupportedEncodingException e) {
		}
		ResultSet rs = new ResultSet();
		String strBuf = "";

		try{
			auth = loadAuth();
		}catch(Exception ex){
			throw new SQLException("Failed to load authentication class: " + ex.getMessage());
		}
		
		if(auth == null){
			strBuf = "Authentication module is missing. Please implement IWsAuthentcation and configure it for WSS";
			throw new SQLException(strBuf);
		}
		
		if(!auth.allow(username, password, bSQL)){
			strBuf = "Access denied.";
			throw new SQLException(strBuf);
		}
		
		try{
			rs = executeQuery(bSQL, rangeStart, rangeEnd);
			rs.setStatusMessage("ok:");
		}catch(Exception ex){
			rs.setStatusMessage("error: " + ex.getLocalizedMessage());
		}
		return rs;
	}
	
	
	/**
	 * This method is called when update request have come from WebService
	 * 
	 * @param bSQL
	 * @param username
	 * @param password
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 */
	public String executeUpdateFromWebService(String bSQL, String username, String password) throws SQLException, Exception {
		try {
			bSQL = new String(bSQL.getBytes(), "UTF-8");
		} catch (UnsupportedEncodingException e) {
		}
		String strRet = "";

		try{
			auth = loadAuth();
		}catch(Exception ex){
			throw new SQLException("Failed to load authentication class: " + ex.getMessage());
		}
		
		if(auth == null){
			strRet = "Authentication module is missing. Please implement IWsAuthentcation and configure it for WSS";
			throw new SQLException(strRet);
		}
		
		if(!auth.allow(username, password, bSQL)){
			strRet = "Access denied.";
			throw new SQLException(strRet);
		}
		
		try{
			strRet = executeUpdate(bSQL);
		}catch(Exception ex){
			strRet = "error: " + ex.getMessage();
		}
		return strRet;
	}

	
	public String executeBatchUpdateFromWebService(List<String> bSQL, String username, String password) throws SQLException, Exception {
		String strRet = "";
		int i = 0;
		ArrayList<String> bSQLbuf = new ArrayList<String>();

		try {
			while(i < bSQL.size()){
				bSQLbuf.add(new String(bSQL.get(i).getBytes(), "UTF-8"));
				i++;
			}
		} catch (UnsupportedEncodingException e) {
		}
		
		i = 0;
		try{
			auth = loadAuth();
		}catch(Exception ex){
			throw new SQLException("Failed to load authentication class: " + ex.getMessage());
		}
		
		if(auth == null){
			strRet = "Authentication module is missing. Please implement IWsAuthentcation and configure it for WSS";
			throw new SQLException(strRet);
		}
		
		while(i < bSQL.size()){
			if(!auth.allow(username, password, bSQL.get(i))){
				strRet = "Access denied.";
				throw new SQLException(strRet);
			}
			i++;
		}
		
		try{
			strRet = executeUpdates(bSQL);
		}catch(Exception ex){
			strRet = "error: " + ex.getMessage();
		}
		return strRet;
	}
	
	/**
	 * This method is used to execute all other sql statements than select and desc table.
	 * 
	 * @param bSQL
	 * @return the return value is null, "ok:" or rowid depending on sql statement.
	 * @throws SQLException
	 * @throws SQLTransactionException
	 * @throws SQLRowNotFoundException
	 * @throws Exception
	 */
	public synchronized String executeUpdate(String bSQL) throws SQLException, SQLTransactionException, SQLRowNotFoundException, Exception {		
		PersistenceManagerInterface pm = null;
		this.checkUpdateAudit(bSQL);
		pm = this.getPersistenceManager(true);
		String strRet = null;
		
		try{
			strRet = executeUpdate(bSQL, pm);
		}
		catch(SQLException trans){
			this.close(pm, true, false);
			throw trans;
		}
		catch(SQLRowIdConflictException trans){
			this.close(pm, true, false);
			throw trans;
		}
		catch(SQLTransactionException trans){
			this.close(pm, true, false);
			throw trans;
		}
		catch(SQLRowNotFoundException rex){
			this.close(pm, true, false);
			throw rex;
		}
		catch(Exception ex){
			this.close(pm, true, false);
			throw ex;
		}
		this.close(pm, true, true);
		return strRet;
	}

	/**
	 * With this method it is possible to execute several updates under one transaction.
	 * 
	 * @param bSQL
	 * @return
	 * @throws SQLException
	 * @throws SQLTransactionException
	 * @throws SQLRowNotFoundException
	 * @throws SQLRowIdConflictException
	 * @throws Exception
	 */
	public synchronized String executeUpdates(List<String> bSQL) throws SQLException, SQLTransactionException, SQLRowNotFoundException, SQLRowIdConflictException, Exception {
		PersistenceManagerInterface pm = this.getPersistenceManager(true);
		int i = 0;
		String strRet = "";
		String delim = "";

		try {
			while(i < bSQL.size()){
				strRet += delim + executeUpdate(bSQL.get(i), pm);
				delim = ":";
				i++;
			}
		}
		catch (SQLException cex) {
			this.close(pm, true, false);
			throw cex;
		}
		catch (SQLTransactionException cex) {
			this.close(pm, true, false);
			throw cex;
		}
		catch (SQLRowNotFoundException cex) {
			this.close(pm, true, false);
			throw cex;
		}
		catch (SQLRowIdConflictException cex) {
			this.close(pm, true, false);
			throw cex;
		}
		catch (Exception ex) {
			this.close(pm, true, false);
			throw ex;
		}
		this.close(pm, true, true);
		return strRet;
	}

	
	private synchronized TableIDInterface getTableId(PersistenceManagerInterface pm) throws SQLException, SQLTransactionException{
		int iRet = -1;

//		TableIDInterface ti = createNewTableID.newInstance(-1);
		TableIDInterface ti = null;
		QueryInterface query = pm.newQuery(TableIDInterface.class);
		List<TableIDInterface> results = (List<TableIDInterface>) query.execute();
		
		if(results.size() == 0){
			ti = createNewTableID.newInstance(1);
			iRet = 1;
			ti.setTableID(iRet);
		}else{
			ti = results.get(0);
			iRet = ti.getTableID() + 1;
			ti.setTableID(iRet);
		}

		pm.makePersistent(ti);
		return ti;
	}


	private int[] getTimeForRowId(){
		int iRet[] = new int[3];
		Calendar cal = Calendar.getInstance();
		String date = "" + cal.get(Calendar.YEAR);
		String time = "" + cal.get(Calendar.HOUR_OF_DAY);
		iRet[2] = cal.get(Calendar.MILLISECOND);
		
		if(cal.get(Calendar.MONTH) + 1 < 10){
			date += "0" + (cal.get(Calendar.MONTH) + 1);
		}else{
			date += "" + (cal.get(Calendar.MONTH) + 1);
		}

		if(cal.get(Calendar.DATE) < 10){
			date += "0" + cal.get(Calendar.DATE);
		}else{
			date += cal.get(Calendar.DATE);
		}		
		iRet[0] = Integer.parseInt(date);
		
		if(cal.get(Calendar.MINUTE) < 10){
			time += "0" + cal.get(Calendar.MINUTE);
		}else{
			time += cal.get(Calendar.MINUTE);
		}
		if(cal.get(Calendar.SECOND) < 10){
			time += "0" + cal.get(Calendar.SECOND);
		}else{
			time += cal.get(Calendar.SECOND);
		}
		iRet[1] = Integer.parseInt(time);
		
		return iRet;
	}
	
	private synchronized String executeUpdate(String bSQL, PersistenceManagerInterface pm) throws SQLException, SQLRowNotFoundException, SQLRowIdConflictException, Exception {
		parser.setPersistentManager(pm);
		int iType = parser.parse(bSQL);
		String strRet = null;
		this.schema = parser.getSchema();

		
		if(iType == SQLParser.CREATE_TABLE_TYPE){
			TableIDInterface ti = this.getTableId(pm);
			int newTableId = ti.getTableID();

			KeyBuilderInterface keyBuilder = this.createNewKeyBuilder.newInstance();
			keyBuilder.setParentKey(ti.getKey());
			keyBuilder.addChild(this.createNewTable.getNameForKey(), newTableId);
			KeyInterface newTableKey = keyBuilder.getKey();
			
			String testTable = parser.getTableName();
		    QueryInterface query = pm.newQuery(TableInterface.class);
		    query.setFilter("tableName == tableNameParam && schema == schemaParam");
		    query.declareParameters("String tableNameParam, String schemaParam");

		    List<TableInterface> results = (List<TableInterface>)query.execute(testTable, schema);
		    if(results.size() > 0){
		    	throw new SQLException("Table already exists: " + schema + "." + testTable);
		    }
		    
			TableInterface newTable = createNewTable.newInstance(newTableId, this.schema, parser.getTableName(), parser.getTableRowSize(), parser.getTableSegMaxRows());
			newTable.setTableKey(newTableKey);
			byte col[] = parser.getColumnInfo().getAsByteArray();
			newTable.setColumnInfo(col);
			pm.makePersistent(newTable);
			strRet = "ok:";
		}

		if(iType == SQLParser.TABLE_INSERT_TYPE){
			int newRowId = -1;
		    int i = 0;
		    int timestamp[] = null;
			parser.setPersistentManager(pm);
			String updateTableName = parser.getTableName();
			TableDataInterface table = this.transactionTables.get(updateTableName);
//			TableDataInterface table = null;
			TableRow tr = null;
			byte rowData[] = null;
			byte data[] = null;
			int iRowCount = 0;
			int updateTableID = parser.getUpdateTableID();
			this.ROWSIZE_MAX = parser.getTableRowSize();
			this.ROWCOUNT_MAX = parser.getTableSegMaxRows();
			
			ColumnInfo ci = parser.getColumnInfo();
			List<String> tableColumns = ci.getColumnNames();
			
			if (table == null || table.getFreeRows() == 0) {
				// no table data tables in "memory hash" or the table in "memory hash" is full
				
				QueryInterface query = pm.newQuery(TableDataInterface.class);
				query.setFilter("tableID == tableIDParam && freeRows > 0");
				query.setOrdering("freeRows asc");
				query.setRange(0, 1);
				query.declareParameters("Integer tableIDParam");

				List<TableDataInterface> results = (List<TableDataInterface>) query.execute(updateTableID);
				if (results.size() < 1) {
					// No table data tables available for new tablerow from datastore
					
					// getting id for tabledata
					TableIDInterface ti = this.getTableId(pm);
					int newTableId = ti.getTableID();
					
					KeyBuilderInterface keyBuilder = this.createNewKeyBuilder.newInstance();
					keyBuilder.setParentKey(ti.getKey());
					keyBuilder.addChild(this.createNewTableData.getNameForKey(), newTableId);
					KeyInterface newTableKey = keyBuilder.getKey();
					
					int newTableDataID = ti.getTableID();
					
					// creating new TableDate
					table = createNewTableData.newInstance(newTableDataID, updateTableID);
					newRowId = 1;
					table.setLastRowid(newRowId);
					
					// setting key containing parent key
					table.setTableKey(newTableKey);
					
					// initializing the whole blob data
					data = new byte[DATA_MAX];
					// this call is made to allow update in memory mode database
					table.getBytes(true);
					// adding data to the new TableData
					table.setDataRows(data);
					
					// writing rowcount to one for new tabledata (= datasegment) 
					ci.writeInt(data, 0, 1);
				} else {
					// valid tabledata table found from datastore
					int iBuf = -1;
					// getting tabledata table
					table = results.get(0);
					
					// increasing the rowcount for retrieved tabledata segment
					newRowId = table.getLastRowid() + 1;
					table.setLastRowid(newRowId);
					
					// Getting datarow from tabledata
					data = table.getDataRows(true);
					
					// reading tot rowcount in segment
					iBuf = ci.readInt(data, 0);
					// increasing segment rowcount by one
					iBuf++;
					// storing new rowcount into segment
					ci.writeInt(data, 0, iBuf);
				}

				// adding new tabledata table to "memory hash"
				this.transactionTables.put(parser.getTableName(), table);
			}else{
				// tabledata segment found from memory hash - this is because the same object
				// must be used all the time in transaction
				int iBuf = -1;
				// increasing the rowcount for retrieved tabledata segment
				newRowId = table.getLastRowid() + 1;
				table.setLastRowid(newRowId);
				
				// getting blob from tabledata table
				data = table.getDataRows(true);
				// increasing segments rowcount by one
				iBuf = ci.readInt(data, 0);
				iBuf++;
				// writing new segments rowcount to bytearry (= blob)
				ci.writeInt(data, 0, iBuf);
			}

			// reading current rowcount in tabledata bolob segment
		    iRowCount = ci.readInt(data, 0);
		    // initializing new rowdata. 12 is for tableid + segmentid + rowid integer valus
		    rowData = new byte[ROWSIZE_MAX + TableRow.ROWID_SIZE];
		    tr = new TableRow(rowData, this.ROWSIZE_MAX);
		    // stting tableid for rowid information
		    tr.setTableID(table.getTableID());
		    // setting tabledataid for rowid information
		    tr.setTableDataID(table.getTableDataID());
		    // setting actual rowid for new row
		    tr.setRowID(newRowId);

		    timestamp = this.getTimeForRowId();
		    tr.setUpdateDate(timestamp[0]);
		    tr.setUpdateTime(timestamp[1]);
		    tr.setUpdateTimeMills(timestamp[2]);
		    
		    // updating visible rowcount in tabledata table
		    table.setRowCount(iRowCount);
		    // updating freerows count in tabledata table. This value is used in query
		    // when finding out tabledata table (datasegment) where to store new row
		    table.setFreeRows(this.ROWCOUNT_MAX - iRowCount);

		    // initializing bytes for new row. For char values the value is 0 (int) and
		    // for number columns = (String)
		    i = 0;
		    while(i < tableColumns.size()){
		    	String colNameBuf = tableColumns.get(i);
		    	int iTypeBuf = ci.getColumnType(colNameBuf);
		    	int iSizeBuf = ci.getColumnLength(colNameBuf);
		    	int iStartBuf = ci.getColumnStartPosition(colNameBuf);
		    	tr.initColumn(iTypeBuf, iStartBuf, iSizeBuf);
		    	i++;
		    }

		    // writing values to new row byte array parsed from sql statement
		    i = 0;
		    List<String> addCols = ci.getUpdateColumns();
		    while(i < addCols.size()){
		    	// getting column name given in sql statement
		    	String colNameBuf = addCols.get(i);
		    	// getting column type
		    	int iTypeBuf = ci.getColumnType(colNameBuf);
		    	// getting column length given in table create statement
		    	int iSizeBuf = ci.getColumnLength(colNameBuf);
		    	int iDecimalsBuf = ci.getColumnDecimals(colNameBuf);
		    	// getting byte offset for column
		    	int iStartBuf = ci.getColumnStartPosition(colNameBuf);
		    	// converting given string value from sql statement to byte array
		    	byte bvalue[] = null;
		    	String value = ci.getColValue(colNameBuf);
		    	// getting rid of ' chars from value
		    	if(value.startsWith("'") && value.endsWith("'")){
		    		value = value.substring(1, value.length() - 1);
		    	}
		    	// getting value as byte array
		    	bvalue = value.getBytes();

		    	// checking that the value is not larger that colsize in table
		    	if(bvalue.length > iSizeBuf){
		    		throw new SQLException("Value length exceed column max size: " + colNameBuf + "/" + value);
		    	}

		    	// writing values to bytearray
		    	tr.writeValue(iTypeBuf, iStartBuf, iSizeBuf, iDecimalsBuf, bvalue);
		    	i++;
		    }

		    
		    // writing bytes to blobs byte array. 4 is because of rowcount in begining of byte array
		    i = 0;
	    	int iWriteByte = ((iRowCount - 1) * (ROWSIZE_MAX + TableRow.ROWID_SIZE)) + 4 + i;
		    while(i < (ROWSIZE_MAX + TableRow.ROWID_SIZE)){
		    	data[iWriteByte + i] = rowData[i];
		    	i++;
		    }

		    table.setDataRows(data);
	    	pm.makePersistent(table);
	    	table = null;
			strRet = this.getRowIdAsString(rowData, ci);
		}

		if(iType == SQLParser.TABLE_UPDATE_TYPE){
			String tableName = parser.getTableName();
			this.schema = parser.getSchema();
			String rowid = parser.getRowid();
			ArrayList<String> updateColumns = parser.getUpdateColumns();
			ColumnInfo ci = parser.getColumnInfo();
			strRet = this.updateDataRow(pm, rowid, ci, tableName, updateColumns);
		}

		
		if(iType == SQLParser.TABLE_DELETE_TYPE){
			String tableName = parser.getTableName();
			this.schema = parser.getSchema();
			String rowid = parser.getRowid();
			ArrayList<String> updateColumns = parser.getUpdateColumns();
			ColumnInfo ci = parser.getColumnInfo();
			strRet = this.deleteDataRow(pm, rowid, ci, tableName, updateColumns);
		}

		if(iType == SQLParser.DROP_TABLE_TYPE){
			TableInterface table = null;
			String dropTable = parser.getTableName();
		    QueryInterface query = pm.newQuery(TableInterface.class);
		    query.setFilter("tableName == tableNameParam && schema == schemaParam");
		    query.declareParameters("String tableNameParam, String schemaParam");

		    QueryInterface query2 = pm.newQuery(TableDataInterface.class);
		    query2.setFilter("tableID == tableIDParam");
		    query2.declareParameters("Integer tableIDParam");

		    List<TableInterface> results = (List<TableInterface>)query.execute(dropTable, schema);
		    if(results.size() < 1){
		    	throw new SQLException("Table does not exists: " + schema + "." + dropTable);
		    }
		    table = results.get(0);

		    List<TableDataInterface> results2 = (List<TableDataInterface>)query2.execute(table.getTableID());

		    try{
		    	pm.deletePersistentAll(results2);
		    	pm.deletePersistent(table);
		    }catch(Exception ex){
		    	throw new SQLTransactionException("Failed to delete table: " + ex.getMessage());
		    }

			strRet = "ok:";
		}
		
		if(strRet == null){
			throw new SQLException("Invalid command type...");
		}

		return strRet;
	}

	private synchronized String updateDataRow(PersistenceManagerInterface pm, String rowid, ColumnInfo ci, String tableNameParam, ArrayList<String> updateColumnsParam) throws SQLException, SQLRowNotFoundException, SQLRowIdConflictException {
		String newRowId = "error: ";
		String tableName = tableNameParam;
		ArrayList<String> updateColumns = updateColumnsParam;
		TableDataInterface table = null;
		String parts[] = rowid.split("\\.");
		int iRowSize = 0;
		int iRows = 0;
		int iRowId = -1;
		byte dataRows[] = null;
		byte dataBuf[] = null;
		int i = 0;
		boolean bRowFound = false;
		
		try{
		    QueryInterface query = pm.newQuery(TableDataInterface.class);
		    query.setFilter("tableDataID == tableDataIDParam");
		    query.declareParameters("Integer tableDataIDParam");

		    List<TableDataInterface> results = (List<TableDataInterface>)query.execute(Integer.parseInt(parts[1]));
		    if(results.size() < 1){
//		    	throw new SQLException("TableData does not exists for rowid: " + parts[1]);
		    	throw new SQLException("TableData does not exists for rowid: " + rowid);
		    }
		    table = results.get(0);
		    iRowSize = parser.getTableRowSize();
			iRows = table.getRowCount();
		    dataRows = table.getDataRows(true);
//		    byte[][] rows = this.getRows(dataRows);
		    iRowId = Integer.parseInt(parts[2]);

		    while(i < iRows){
		    	// + 4 is because of rowcount at the begining of blob binary array
		    	dataBuf = ci.readBytes(dataRows, i*(iRowSize + TableRow.ROWID_SIZE) + 4, (iRowSize + TableRow.ROWID_SIZE));
		    	int iTargetRowId = ci.readInt(dataBuf, 8);
		    	if(iRowId == iTargetRowId){
		    		bRowFound = true;
		    		int iCurDate = ci.readInt(dataBuf, 12);
		    		int iCurTime = ci.readInt(dataBuf, 16);
		    		int iCurMill = ci.readInt(dataBuf, 20);
		    		int iUpdateDate = Integer.parseInt(parts[3]);
		    		int iUpdateTime = Integer.parseInt(parts[4]);
		    		int iUpdateMills = Integer.parseInt(parts[5]);
		    		
		    		if(iCurDate == iUpdateDate &&
		    				iCurTime == iUpdateTime &&
		    				iCurMill == iUpdateMills
		    				){
		    			int newStamp[] = this.getTimeForRowId();
		    			List<String> updateCols = ci.getUpdateColumns();
		    			int ii = 0;
	    				TableRow tr = new TableRow(dataBuf);
		    			while(ii < updateCols.size()){
		    				String col = updateCols.get(ii);
		    				String value = ci.getColValue(col);
		    				byte bvalue[] = value.getBytes();
		    				tr.writeValue(ci.getColumnType(col), ci.getColumnStartPosition(col), ci.getColumnLength(col), ci.getColumnDecimals(col), bvalue);
		    				ii++;
		    			}
		    			tr.setUpdateDate(newStamp[0]);
		    			tr.setUpdateTime(newStamp[1]);
		    			tr.setUpdateTimeMills(newStamp[2]);
				    	ci.writeBytes(dataRows, i*(iRowSize + TableRow.ROWID_SIZE) + 4, dataBuf);
				    	newRowId = this.getRowIdAsString(dataBuf, ci);
//		    			i = iRows + 1;
				    	break;
		    		}else{

		    			List<String> cols = ci.getColumnNames();
		    			Hashtable<String, String> values = new Hashtable<String, String>();
		    			TableRow trBuf = new TableRow(dataBuf);
		    			int iex = 0;
		    			while(iex < cols.size()){
		    				String name = cols.get(iex);
		    				String value = new String(trBuf.getColBytes(ci.getColumnStartPosition(name), ci.getColumnLength(name)));
		    				values.put(name, value);
		    				iex++;
		    			}

		    			throw new SQLRowIdConflictException("Timestamp is different in database than in update row for rowid: " + rowid, cols, values, iCurDate, iCurTime, iCurMill);

		    		}
		    		
		    		
		    	}

		    	i++;
		    }
		    
		    if(!bRowFound){
		    	throw new SQLRowNotFoundException("Row not found for rowid: " + rowid);
		    }

		    table.touch(new java.util.Date().getTime());
		    table.setDataRows(dataRows);
		    pm.makePersistent(table);
		    
		}
		catch(SQLRowNotFoundException rex){
			throw rex;
		}
		catch(Exception ex){
			ex.printStackTrace();
//			throw new SQLException("update for rowid failed: " + parts[1] + " " + ex.getMessage());
			throw new SQLException("update for rowid failed: " + rowid + " " + ex.getMessage());
		}
		
		return newRowId;
	}

	
	private synchronized String deleteDataRow(PersistenceManagerInterface pm, String rowid, ColumnInfo ci, String tableNameParam, ArrayList<String> updateColumnsParam) throws SQLException, SQLRowNotFoundException, SQLRowIdConflictException {
		String newRowId = "error: ";
		String tableName = tableNameParam;
		ArrayList<String> updateColumns = updateColumnsParam;
		TableDataInterface table = null;
		String parts[] = rowid.split("\\.");
		int iRowSize = 0;
		int iRows = 0;
		int iRowId = -1;
		byte dataRows[] = null;
		byte oneMissingRows[] = null;
		byte dataBuf[] = null;
		int i = 0;
		int iCursor = 0;
		boolean bRowFound = false;
		
		try{
		    QueryInterface query = pm.newQuery(TableDataInterface.class);
		    query.setFilter("tableDataID == tableDataIDParam");
		    query.declareParameters("Integer tableDataIDParam");

		    List<TableDataInterface> results = (List<TableDataInterface>)query.execute(Integer.parseInt(parts[1]));
		    if(results.size() < 1){
		    	throw new SQLException("TableData does not exists for rowid: " + parts[1]);
		    }
		    table = results.get(0);
		    iRowSize = parser.getTableRowSize();
			iRows = table.getRowCount();
		    dataRows = table.getDataRows(true);
		    oneMissingRows = new byte[dataRows.length];
		    ci.writeInt(oneMissingRows, 0, iRows - 1);
		    iCursor = iCursor + 4;
		    table.setRowCount(iRows - 1);
		    
//		    byte[][] rows = this.getRows(dataRows);
		    iRowId = Integer.parseInt(parts[2]);

		    while(i < iRows){
		    	// + 4 is because of rowcount at the begining of blob binary array
		    	dataBuf = ci.readBytes(dataRows, i*(iRowSize + TableRow.ROWID_SIZE) + 4, (iRowSize + TableRow.ROWID_SIZE));
		    	int iTargetRowId = ci.readInt(dataBuf, 8);
		    	
		    	if(iRowId != iTargetRowId){
//				    ci.writeInt(oneMissingRows, 0, iRows - 1);
				    ci.writeBytes(oneMissingRows, iCursor, dataBuf);
				    iCursor = iCursor + dataBuf.length;
		    	}
	
				if (!bRowFound) {

					if (iRowId == iTargetRowId) {
						bRowFound = true;
						int iCurDate = ci.readInt(dataBuf, 12);
						int iCurTime = ci.readInt(dataBuf, 16);
						int iCurMill = ci.readInt(dataBuf, 20);
						int iUpdateDate = Integer.parseInt(parts[3]);
						int iUpdateTime = Integer.parseInt(parts[4]);
						int iUpdateMills = Integer.parseInt(parts[5]);

						if (iCurDate == iUpdateDate && iCurTime == iUpdateTime
								&& iCurMill == iUpdateMills) {
							/*
							 * int newStamp[] = this.getTimeForRowId();
							 * List<String> updateCols = ci.getUpdateColumns();
							 * int ii = 0; TableRow tr = new TableRow(dataBuf);
							 * while(ii < updateCols.size()){ String col =
							 * updateCols.get(ii); String value =
							 * ci.getColValue(col); byte bvalue[] =
							 * value.getBytes();
							 * tr.writeValue(ci.getColumnType(col),
							 * ci.getColumnStartPosition(col),
							 * ci.getColumnLength(col),
							 * ci.getColumnDecimals(col), bvalue); ii++; }
							 * tr.setUpdateDate(newStamp[0]);
							 * tr.setUpdateTime(newStamp[1]);
							 * tr.setUpdateTimeMills(newStamp[2]);
							 * ci.writeBytes(dataRows, i*(iRowSize +
							 * TableRow.ROWID_SIZE) + 4, dataBuf); newRowId =
							 * this.getRowIdAsString(dataBuf, ci); // i = iRows
							 * + 1;
							 */
							//break;
						} else {

							List<String> cols = ci.getColumnNames();
							Hashtable<String, String> values = new Hashtable<String, String>();
							TableRow trBuf = new TableRow(dataBuf);
							int iex = 0;
							while (iex < cols.size()) {
								String name = cols.get(iex);
								String value = new String(trBuf.getColBytes(
										ci.getColumnStartPosition(name),
										ci.getColumnLength(name)));
								values.put(name, value);
								iex++;
							}

							throw new SQLRowIdConflictException(
									"Timestamp is different in database than in delete row for rowid: "
											+ rowid, cols, values, iCurDate,
									iCurTime, iCurMill);

						}
					}

				}

		    	i++;
		    }

		    ci.writeInt(oneMissingRows, iCursor, -1);
		    
		    if(!bRowFound){
		    	throw new SQLRowNotFoundException("Row not found for rowid: " + rowid);
		    }

		    table.touch(new java.util.Date().getTime());
		    table.setDataRows(oneMissingRows);
		    pm.makePersistent(table);
		    
		}
		catch(SQLRowNotFoundException rex){
			throw rex;
		}
		catch(Exception ex){
			ex.printStackTrace();
			throw new SQLException("delete for rowid failed: " + parts[1] + " " + ex.getMessage());
		}
		
		return newRowId;
	}
	
	
}
